//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>
package org.jpgolay.wi.persistence;

import java.io.PrintStream;
import java.sql.*;
import org.jpgolay.wi.propertie.PropertyCRUD;

public class UtilityDB {

    public UtilityDB() {
        prop = new PropertyCRUD();
    }

    public void createMysqlDB() {
        String dbwi = "CREATE DATABASE IF NOT EXISTS " + prop.getDataBaseHome() + ";";
        String wiUser = "GRANT ALL PRIVILEGES ON " + prop.getDataBaseHome() + ".* TO " + prop.getUser() + "@'%' IDENTIFIED BY '" + prop.getPass() + "' WITH GRANT OPTION;";
        String tableUser = "CREATE TABLE USER ( ID INTEGER NOT NULL PRIMARY KEY,GROUP_ID INTEGER,USERNAME VARCHAR(255),PASS VARCHAR(255),LASTNAME VARCHAR(255),FIRSTNAME VARCHAR(255),ADDR_LN1 VARCHAR(255),ADDR_LN2 VARCHAR(255),CITY VARCHAR(255),COUNTY VARCHAR(255),ZIP VARCHAR(255),TEL1 VARCHAR(255),TEL2 VARCHAR(255),FAX VARCHAR(255),EMAIL VARCHAR(255),WEB VARCHAR(255),AGENCY_ID INTEGER,ACCESS INTEGER,EXPIRY_DATE VARCHAR(255),PAYMENT_DUE DECIMAL(10,2),COMMENT VARCHAR(255),LAST_UPDATE VARCHAR(255),LAST_USER VARCHAR(255),LOGO MEDIUMBLOB );";
        String tableRealEstate = "CREATE TABLE REALESTATE ( ADDR_LN1 VARCHAR(255),ADDR_LN2 VARCHAR(255),AGENCY_FEES DECIMAL(10,2),ARCHIVE VARCHAR(255),AREA VARCHAR(255),ASPECT VARCHAR(255),AVAILABILITY VARCHAR(255),BALCONY VARCHAR(255),BATCH VARCHAR(255),BATHROOM VARCHAR(255),BED VARCHAR(255),BEDROOM VARCHAR(255),CATEGORY VARCHAR(255),CELLAR VARCHAR(255),CHARGE DECIMAL(10,2),CHARGE_DEPOSIT DECIMAL(10,2),CONDITIONS VARCHAR(255),COUNTY VARCHAR(255),COUNTRY VARCHAR(255),CREATEDATE VARCHAR(255),CUPBOARD VARCHAR(255),DEPARTURE VARCHAR(255),DEPARTURE_CUSTOMER VARCHAR(255),DEPOSIT DECIMAL(10,2),DESCRIPTION VARCHAR(255),ELEVATOR VARCHAR(255),FIREPLACE VARCHAR(255),FLOOR VARCHAR(255),FURNISHED VARCHAR(255),GARAGE VARCHAR(255),GARDEN VARCHAR(255),HALL VARCHAR(255),HEATING VARCHAR(255),ID INTEGER NOT NULL PRIMARY KEY,ID_CUSTOMER INTEGER,BUNCHKEY VARCHAR(255),KITCHEN VARCHAR(255),LAST_UPDATE VARCHAR(255),LAST_USER VARCHAR(255),LIFEANNUITY VARCHAR(255),LIVINGROOM VARCHAR(255),LOGGIA VARCHAR(255),MEZZANINE VARCHAR(255),NUM_CONSULT_CC INTEGER,NUM_CONSULT_WEB INTEGER,OBSERVATION VARCHAR(255),ONLYOPTION VARCHAR(255),OPTION_FISC VARCHAR(255),PANEL VARCHAR(255),PARKING VARCHAR(255),PHOTO1 MEDIUMBLOB,PHOTO2 MEDIUMBLOB,PHOTO3 MEDIUMBLOB,PHOTO4 MEDIUMBLOB,PHOTO5 MEDIUMBLOB,PHOTO6 MEDIUMBLOB,PHOTO7 MEDIUMBLOB,PHOTO8 MEDIUMBLOB,PHOTO9 MEDIUMBLOB,PHOTO10 MEDIUMBLOB,PHOTOS_VALID VARCHAR(255),POOL VARCHAR(255),PLOT VARCHAR(255),PRINT_OBSERVATION VARCHAR(255),REF INTEGER,REF_INTERNAL VARCHAR(255),RENT DECIMAL(10,2),RESERVE VARCHAR(255),RESTROOM VARCHAR(255),ROOFGARDEN VARCHAR(255),SALE DECIMAL(10,2),SECTOR VARCHAR(255),SHOWER VARCHAR(255),SORT VARCHAR(255),STANDING VARCHAR(255),STATE VARCHAR(255),STYLEFLOOR VARCHAR(255),SURFACE_FLOOR INTEGER,SURFACE_GROUND INTEGER,TV VARCHAR(255),TYPE VARCHAR(255),VACATION VARCHAR(255),WEB VARCHAR(255),ZIP VARCHAR(255),AGENCY_ID INTEGER,EXIT_CUSTOMER VARCHAR(255),S_GARAGE INTEGER,S_LIVINGROOM INTEGER,S_HALL INTEGER,S_ROOFGARDEN INTEGER,S_MEZZANINE INTEGER,S_LOGGIA INTEGER,S_ROOM INTEGER,S_CELLAR INTEGER,CUSTOMER VARCHAR(255),RESIDANCE VARCHAR(255),S_KITCHEN INTEGER,S_BALCONY INTEGER,CELLAR_WINE VARCHAR(255),WALLS VARCHAR(255),DUPLEX VARCHAR(255),S_CELLAR_WINE INTEGER );";
        String tableAgency = "CREATE TABLE AGENCY ( ID INTEGER NOT NULL PRIMARY KEY,GROUP_ID INTEGER,USERNAME VARCHAR(255),PASS VARCHAR(255),COMPAGNY VARCHAR(255),STATUS VARCHAR(255),ADDR_LN1 VARCHAR(255),ADDR_LN2 VARCHAR(255),CITY VARCHAR(255),COUNTY VARCHAR(255),ZIP VARCHAR(255),TEL1 VARCHAR(255),TEL2 VARCHAR(255),FAX VARCHAR(255),EMAIL VARCHAR(255),WEB VARCHAR(255),ACCESS INTEGER,WWW VARCHAR(255),WELCOME_EMAIL VARCHAR(255),WELCOME_TITLE VARCHAR(255),NEW_AD_EMAIL_UP VARCHAR(255),NEW_AD_EMAIL_DOWN VARCHAR(255),NEW_AD_TITLE VARCHAR(255),SMTP VARCHAR(255),EXPIRY_DATE VARCHAR(255),PAYMENT_DUE DECIMAL(10,2),COMMENT VARCHAR(255),LAST_UPDATE VARCHAR(255),LAST_USER VARCHAR(255),SIRET VARCHAR(255),NAF VARCHAR(255),LOGO MEDIUMBLOB );";
        String tableGroup = "CREATE TABLE GROUPS ( ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(255),ROLE VARCHAR(255),LAST_UPDATE VARCHAR(255),LAST_USER VARCHAR(255),NOTE VARCHAR(255) );";
        String tableAvailability = "CREATE TABLE AVAILABILITY ( ID INTEGER NOT NULL PRIMARY KEY,REALESTATE_ID INTEGER,CUSTOMER_ID INTEGER,BEGINDATE VARCHAR(30),ENDDATE VARCHAR(30),TAKEOPTION VARCHAR(20),CONFIRMED VARCHAR(20),LAST_UPDATE VARCHAR(255),LAST_USER VARCHAR(255),NOTE VARCHAR(255) );";
        String tableCustomer = "CREATE TABLE CUSTOMER (ID INTEGER NOT NULL PRIMARY KEY,STATUS VARCHAR(50), FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50), ADDRLN1 VARCHAR(100), ADDRLN2 VARCHAR(100), ZIP VARCHAR(20), CITY VARCHAR(50), STATE VARCHAR(50), COUNTRY VARCHAR(50), TEL VARCHAR(20), CEL VARCHAR(20), FAX VARCHAR(20), EMAIL VARCHAR(50), WWW VARCHAR(100), BANK VARCHAR(20), BANKNUMBER VARCHAR(20), PAYMENTBY VARCHAR(20), CARDNAME VARCHAR(50), CARDNUMBER VARCHAR(20), CARDEXPIRYDATE VARCHAR(30), CARDDIGICODE VARCHAR(10), NOTES VARCHAR(255), LASTUSERID VARCHAR(50), LASTTIME VARCHAR(50),SALESPERSONID INTEGER );";
        String tableSalesPerson = "CREATE TABLE SALESPERSON (ID INTEGER NOT NULL PRIMARY KEY,LASTNAME VARCHAR(50), FIRSTNAME VARCHAR(50), ADDRLN1 VARCHAR(100), ADDRLN2 VARCHAR(100), ZIP VARCHAR(20), CITY VARCHAR(50), COUNTRY VARCHAR(50), AREA VARCHAR(50), TEL VARCHAR(20), CEL VARCHAR(20), FAX VARCHAR(20), EMAIL VARCHAR(100), LEVEL VARCHAR(50), SALESRATE DECIMAL(10,2), TOTALSALESRATE DECIMAL(10,2), RESULTS DECIMAL(10,2), RESULTSDATE DATE, OBJECTIF DECIMAL(10,2), OBJECTIFDATE DATE, NOTES VARCHAR(255), LASTUSERID VARCHAR(50), LASTTIME TIMESTAMP,SALESPERSONFAMILY VARCHAR(50));";
        String defaultValueGroup = "INSERT INTO GROUPS (ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER) VALUES (1,'Group default','Role default','modifier ce groupe','2004-01-01 00:00:00','1');";
        String defaultValueAgency = "INSERT INTO AGENCY (ID,GROUP_ID,USERNAME,PASS,COMPAGNY,LAST_UPDATE,LAST_USER,PAYMENT_DUE,ACCESS) VALUES (1,1,'admin','wi','Trial agency','2004-01-01 00:00:00','1',0,0);";
        String defaultValueUser = "INSERT INTO USER (ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,AGENCY_ID,LAST_UPDATE,LAST_USER,PAYMENT_DUE,ACCESS) VALUES (1,1,'webimmo','admin','Webimmo','Trial',1,'2004-01-01 00:00:00','1',0,16793599);";
        try {
            initDB();
            firstConnectDB();
            st = con.createStatement();
            st.execute(dbwi);
            st.execute(wiUser);
            st.close();
            con.close();
            connectDB();
            st = con.createStatement();
            st.execute(tableUser);
            st.execute(tableRealEstate);
            st.execute(tableAgency);
            st.execute(tableGroup);
            st.execute(tableAvailability);
            st.execute(tableCustomer);
            st.execute(tableSalesPerson);
            st.execute(defaultValueGroup);
            st.execute(defaultValueAgency);
            st.execute(defaultValueUser);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return;
    }

    public void deleteMysqlDB() {
        String dbwi = "DROP DATABASE DBWI;";
        String tableUser = "DROP TABLE USER";
        String tableRealEstate = "DROP TABLE REALESTATE";
        String tableAgency = "DROP TABLE AGENCY";
        String tableGroup = "DROP TABLE GROUPS";
        String tableAvailability = "DROP TABLE AVAILABILITY";
        String tableCustomer = "DROP TABLE CUSTOMER";
        String tableSalesPerson = "DROP TABLE SALESPERSON";
        try {
            initDB();
            connectDB();
            st = con.createStatement();
            st.execute(tableUser);
            st.execute(tableRealEstate);
            st.execute(tableAgency);
            st.execute(tableGroup);
            st.execute(tableAvailability);
            st.execute(tableCustomer);
            st.execute(tableSalesPerson);
            st.close();
            con.close();
            firstConnectDB();
            st = con.createStatement();
            st.execute(dbwi);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return;
    }

    private void firstConnectDB() {
        try {
            con = DriverManager.getConnection(prop.getFirstUrl(), prop.getFirstUser(), prop.getFirstPass());
        } catch (SQLException sqlex) {
            System.out.println("Impossible d'ouvrir une (premiere) connection sur la base de donn\351e !");
            System.out.println("\t\turl : " + prop.getFirstUrl());
            System.out.println("\t   user : " + prop.getFirstUser());
            System.out.println("\t   pass : " + prop.getFirstPass());
            System.out.println(sqlex);
        }
    }

    private void connectDB() {
        try {
            con = DriverManager.getConnection(prop.getUrl(), prop.getUser(), prop.getPass());
        } catch (SQLException sqlex) {
            System.out.println("Impossible d'ouvrir une connection sur la base de donn\351e !");
            System.out.println("\t\turl : " + prop.getUrl());
            System.out.println("\t   user : " + prop.getUser());
            System.out.println("\t   pass : " + prop.getPass());
            System.out.println(sqlex);
        }
    }

    private void initDB() {
        try {
            Class.forName(prop.getDriver());
        } catch (ClassNotFoundException cnfex) {
            System.out.println("Impossible de charger le driver JDBC !\ndriver : " + prop.getDriver());
            System.out.println(cnfex);
        }
    }

    public static final void main(String args[]) {
        new UtilityDB();
    }
    private PropertyCRUD prop;
    private Connection con;
    private Statement st;
}
